// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Text;
using Kailua.net.windward.utils;
using net.windward.utils.ado.ODBC;
using NUnit.Framework;

// test all metadata including PK:FK for identically named tables under different schemas. Can use KailuaTest DB.

namespace net.windward.utils.ado
{
	[TestFixture]
	public class TestOdbcProvider
	{
		private const string accessOdbcProvider = "Microsoft Access Driver (*.mdb, *.accdb)";
		private string accessServerName;
		private string accessUsername;
		private string accessPassword;

		private const string db2OdbcProvider = "IBM DB2 ODBC DRIVER - IBMDBCL1";
		private string db2ServerName;
		private string db2DatabaseName;
		private string db2Username;
		private string db2Password;

		private const string mySqlOdbcProvider = "MySQL ODBC 5.1 Driver";
		private string mySqlServerName;
		private string mySqlDatabaseName;
		private string mySqlUsername;
		private string mySqlPassword;

		private string oracleOdbcProvider = "Microsoft ODBC for Oracle";
		private string oracleServerName;
		private string oracleUsername;
		private string oraclePassword;

		private const string postgreOdbcProvider = "PostgreSQL ANSI";
		private string postgreServerName;
		private string postgreDatabaseName;
		private string postgreUsername;
		private string postgrePassword;

		private const string sqlServerOdbcProvider = "SQL Server";
		private string sqlServerServerName;
		private string sqlServerDatabaseName;
		private string sqlServerUsername;
		private string sqlServerPassword;

		readonly DbProviderFactory providerFactory = DbProviderFactories.GetFactory(WrOdbcVendor.FactoryClass);

		[SetUp]
		public void Init()
		{
			NameValueCollection appSettings = ConfigurationManager.AppSettings;

			accessServerName = Path.GetFullPath(appSettings["AccessServer"]);
			accessUsername = appSettings["AccessUsername"];
			accessPassword = appSettings["AccessPassword"];

			db2ServerName = appSettings["DB2Server"];
			db2DatabaseName = appSettings["DB2Database"];
			db2Username = appSettings["DB2Username"];
			db2Password = appSettings["DB2Password"];

			mySqlServerName = appSettings["MySqlServer"];
			mySqlDatabaseName = appSettings["MySqlDatabase"];
			mySqlUsername = appSettings["MySqlUsername"];
			mySqlPassword = appSettings["MySqlPassword"];

			oracleServerName = appSettings["OracleServer"];
			oracleUsername = appSettings["OracleUsername"];
			oraclePassword = appSettings["OraclePassword"];
			WrOdbcVendor vendor = (WrOdbcVendor)WrProviderFactories.CreateProvider("My ODBC Server name", null, WrOdbcVendor.FactoryClass);
			WrVendor.OdbcDriverInfo[] providers = vendor.GetOdbcProviders();
			foreach (WrVendor.OdbcDriverInfo odi in providers)
				if (odi.Driver.Contains("OraClient"))
				{
					oracleOdbcProvider = odi.Driver;
					break;
				}

			postgreServerName = appSettings["PostgreServer"];
			postgreDatabaseName = appSettings["PostgreDatabase"];
			postgreUsername = appSettings["PostgreUsername"];
			postgrePassword = appSettings["PostgrePassword"];

			sqlServerServerName = appSettings["SqlServerServer"];
			sqlServerDatabaseName = appSettings["SqlServerDatabase"];
			sqlServerUsername = appSettings["SqlServerUsername"];
			sqlServerPassword = appSettings["SqlServerPassword"];
		}

		[Test]
		public void TestCreateProvider()
		{
			WrOdbcVendor vendor = (WrOdbcVendor)WrProviderFactories.CreateProvider("My ODBC Server name", null, WrOdbcVendor.FactoryClass);
			Assert.IsNotNull(vendor);
			Assert.AreEqual(false, vendor.CanEnumerateDatabases);
			Assert.AreEqual(false, vendor.CanLaunchAdministrator);
			Assert.AreEqual(false, vendor.CanRunScripts);
			Assert.AreEqual(true, vendor.CanUseTrustedConnection);
			Assert.AreEqual(WrVendor.DATABASE_MODE.OPTIONAL, vendor.DatabaseMode);
			Assert.AreEqual(true, vendor.HasProviders);
			Assert.AreEqual("My ODBC Server name", vendor.Name);

			// enum providers - and make sure we have them all
			WrVendor.OdbcDriverInfo[] providers = vendor.GetOdbcProviders();
			Assert.IsTrue(providers.Length >= 3);
			bool gotAccess = false, gotDb2 = false, gotMySql = false, gotOracle = false, gotPostgre = false, gotSqlServer=false;
			foreach (WrVendor.OdbcDriverInfo info in providers)
				switch (info.Driver)
				{
					case accessOdbcProvider:
						gotAccess = true;
						break;
					case db2OdbcProvider:
						gotDb2 = true;
						break;
					case mySqlOdbcProvider:
						gotMySql = true;
						break;
					case postgreOdbcProvider:
						gotPostgre = true;
						break;
					case sqlServerOdbcProvider:
						gotSqlServer = true;
						break;
					default:
						if (info.Driver == oracleOdbcProvider)
							gotOracle = true;
						break;
				}

			// Access
			if (IntPtr.Size == 4)
				Assert.IsTrue(gotAccess, "do not have Access ODBC driver installed");
			else
				Assert.IsFalse(gotAccess, "Access 64-bit ODBC driver installed - huh?");
			vendor.OdbcProvider = accessOdbcProvider;
			if (! string.IsNullOrEmpty(accessUsername))
				Assert.AreEqual(string.Format("Driver={{{0}}};Dbq={1};Uid={2};Pwd={3}", accessOdbcProvider, accessServerName, accessUsername, accessPassword),
					vendor.ConnectionString(accessServerName, null, new WrCredentials(accessUsername, accessPassword), true));
			else
				Assert.AreEqual(string.Format("Driver={{{0}}};Dbq={1}", accessOdbcProvider, accessServerName),
			vendor.ConnectionString(accessServerName, null, new WrCredentials(accessUsername, accessPassword), true));
			Assert.AreEqual(string.Format("Driver={{{0}}};Dbq={1}", accessOdbcProvider, accessServerName),
				vendor.ConnectionString(accessServerName, null, new WrCredentials(), true));

			// DB2
			Assert.IsTrue(gotDb2, "do not have DB2 ODBC driver installed");
			vendor.OdbcProvider = db2OdbcProvider;
			Assert.AreEqual(string.Format("Driver={{{0}}};Hostname={1};Database={2};Protocol=TCPIP;Uid={3};Pwd={4}", db2OdbcProvider, db2ServerName, db2DatabaseName, db2Username, db2Password),
				vendor.ConnectionString(db2ServerName, db2DatabaseName, new WrCredentials(db2Username, db2Password), true));
			Assert.AreEqual(string.Format("Driver={{{0}}};Hostname={1};Database={2};Protocol=TCPIP", db2OdbcProvider, db2ServerName, db2DatabaseName),
				vendor.ConnectionString(db2ServerName, db2DatabaseName, new WrCredentials(), true));

			// MySql
			Assert.IsTrue(gotMySql, "do not have MySql ODBC driver installed");
			vendor.OdbcProvider = mySqlOdbcProvider;
			Assert.AreEqual(string.Format("Driver={{{0}}};Server={1};Database={2};User={3};Password={4}", mySqlOdbcProvider, mySqlServerName, mySqlDatabaseName, mySqlUsername, mySqlPassword),
				vendor.ConnectionString(mySqlServerName, mySqlDatabaseName, new WrCredentials(mySqlUsername, mySqlPassword), true));
			Assert.AreEqual(string.Format("Driver={{{0}}};Server={1};Database={2}", mySqlOdbcProvider, mySqlServerName, mySqlDatabaseName),
				vendor.ConnectionString(mySqlServerName, mySqlDatabaseName, new WrCredentials(), true));

			// Oracle
			Assert.IsTrue(gotOracle, "do not have Oracle ODBC driver installed");
			vendor.OdbcProvider = oracleOdbcProvider;
			Assert.AreEqual(string.Format("Driver={{{0}}};Dbq={1};Uid={2};Pwd={3}", oracleOdbcProvider, oracleServerName, oracleUsername, oraclePassword),
				vendor.ConnectionString(oracleServerName, null, new WrCredentials(oracleUsername, oraclePassword), true));
			Assert.AreEqual(string.Format("Driver={{{0}}};Dbq={1}", oracleOdbcProvider, oracleServerName),
				vendor.ConnectionString(oracleServerName, null, new WrCredentials(), true));

			// PostgreSQL
			Assert.IsTrue(gotPostgre, "do not have PostgreSQL ODBC driver installed");
			vendor.OdbcProvider = postgreOdbcProvider;
			Assert.AreEqual(string.Format("Driver={{{0}}};Server={1};Database={2};Uid={3};Pwd={4}", postgreOdbcProvider, postgreServerName, postgreDatabaseName, postgreUsername, postgrePassword),
				vendor.ConnectionString(postgreServerName, postgreDatabaseName, new WrCredentials(postgreUsername, postgrePassword), true));
			Assert.AreEqual(string.Format("Driver={{{0}}};Server={1};Database={2}", postgreOdbcProvider, postgreServerName, postgreDatabaseName),
				vendor.ConnectionString(postgreServerName, postgreDatabaseName, new WrCredentials(), true));

			// Sql Server
			Assert.IsTrue(gotSqlServer, "do not have Sql Server ODBC driver installed");
			vendor.OdbcProvider = sqlServerOdbcProvider;
			Assert.AreEqual(string.Format("Driver={{{0}}};Server={1};Database={2};Uid={3};Pwd={4}", sqlServerOdbcProvider, sqlServerServerName, sqlServerDatabaseName, sqlServerUsername, sqlServerPassword),
				vendor.ConnectionString(sqlServerServerName, sqlServerDatabaseName, new WrCredentials(sqlServerUsername, sqlServerPassword), true));
			Assert.AreEqual(string.Format("Driver={{{0}}};Server={1};Database={2};Trusted_Connection=yes", sqlServerOdbcProvider, sqlServerServerName, sqlServerDatabaseName),
				vendor.ConnectionString(sqlServerServerName, sqlServerDatabaseName, new WrCredentials(), true));
		}

		[Test]
		public void TestAccessEnumeration()
		{

			if (IntPtr.Size != 4)
				return;

			// get the database
			WrOdbcServer server = new WrOdbcServer(providerFactory, accessOdbcProvider, accessServerName);
			IWrDatabase db = server.CreateDatabase(accessServerName);

			// Northwind tables
			db.LoadMetadata(new WrCredentials(accessUsername, accessPassword), WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			WrTable[] tables = db.Tables;
			Assert.AreEqual(8, tables.Length);
			Assert.AreEqual("Categories", tables[0].FullName);
			Assert.IsFalse(tables[0].IsSystem);
			Assert.AreEqual("Suppliers", tables[7].FullName);
			Assert.IsFalse(tables[7].IsSystem);

			// Northwind views
			WrView[] views = db.Views;
			Assert.AreEqual(18, views.Length);
			Assert.AreEqual("Alphabetical List of Products", views[0].FullName);
			Assert.IsFalse(views[0].IsSystem);
			Assert.AreEqual("Invoices Filter", views[6].FullName);
			Assert.IsFalse(views[6].IsSystem);

			// Northwind.Orders columns
			WrColumn[] cols = db.Tables[4].Columns;
			Assert.AreEqual(14, cols.Length);
			Assert.AreEqual("OrderID", cols[0].FullName);
			Assert.AreEqual(DbType.Int32, cols[0].DbType);
			Assert.AreEqual(null, cols[0].PrimaryKey);

			Assert.AreEqual("CustomerID", cols[1].FullName);
			Assert.AreEqual(DbType.AnsiString, cols[1].DbType);
			//bugbug			Assert.AreEqual("Customers.CustomerID", cols[1].PrimaryKey);

			Assert.AreEqual("EmployeeID", cols[2].FullName);
			Assert.AreEqual(DbType.Int32, cols[2].DbType);
			//bugbug			Assert.AreEqual("Employees.EmployeeID", cols[2].PrimaryKey);

			Assert.AreEqual("ShipCountry", cols[13].FullName);
			Assert.AreEqual(DbType.AnsiString, cols[13].DbType);
			Assert.AreEqual(null, cols[13].PrimaryKey);
		}

		[Test]
		public void TestDB2Enumeration()
		{

			// get the database
			WrOdbcServer server = new WrOdbcServer(providerFactory, db2OdbcProvider, db2ServerName);
			IWrDatabase db = server.CreateDatabase(db2DatabaseName);
			WrCredentials cred = new WrCredentials(db2Username, db2Password);

			// SAMPLE tables
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER, null);
			WrTable[] tables = db.Tables;
			Assert.AreEqual(11, tables.Length);
			Assert.AreEqual("CL_SCHED", tables[0].FullName);
			Assert.IsFalse(tables[0].IsSystem);
			Assert.AreEqual("STAFF", tables[10].FullName);
			Assert.IsFalse(tables[10].IsSystem);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.SYSTEM, null);
			tables = db.Tables;
			Assert.AreEqual(105, tables.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.ALL, null);
			tables = db.Tables;
			Assert.AreEqual(111, tables.Length);

			// SAMPLE views
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER, null);
			WrView[] views = db.Views;
			Assert.AreEqual(0, views.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.SYSTEM, null);
			views = db.Views;
			Assert.AreEqual(155, views.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.ALL, null);
			views = db.Views;
			Assert.AreEqual(155, views.Length);

			// SAMPLE.EMPLOYEE columns
			Trap.trap();
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER, null);
			WrColumn[] cols = db.Tables[4].Columns;
			Assert.AreEqual(14, cols.Length);
			Assert.AreEqual("EMPNO", cols[0].FullName);
			Assert.AreEqual(DbType.String, cols[0].DbType);
			Assert.AreEqual("COMM", cols[13].FullName);
			Assert.AreEqual(DbType.Decimal, cols[13].DbType);

			// SAMPLE stored procedures
			WrStoredProcedure[] procs = db.StoredProcedures;
			Assert.AreEqual(0, procs.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.SYSTEM, null);
			procs = db.StoredProcedures;
			Assert.AreEqual(84, procs.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.ALL, null);
			procs = db.StoredProcedures;
			Assert.AreEqual(84, procs.Length);
		}

		[Test]
		public void TestMySqlEnumeration()
		{

			// enum databases, make sure has test one
			// get the database
			WrOdbcServer server = new WrOdbcServer(providerFactory, mySqlOdbcProvider, mySqlServerName);
			IWrDatabase db = server.CreateDatabase(mySqlDatabaseName);
			WrCredentials cred = new WrCredentials(mySqlUsername, mySqlPassword);

			// Sakila tables
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.ALL, null);
			WrTable[] tables = db.Tables;
			Assert.AreEqual(16, tables.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.SYSTEM, null);
			tables = db.Tables;
			Assert.AreEqual(0, tables.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER, null);
			tables = db.Tables;
			Assert.AreEqual(16, tables.Length);
			Assert.AreEqual("actor", tables[0].FullName);
			Assert.IsFalse(tables[0].IsSystem);
//bugbug - all the descriptions are wrong			Assert.AreEqual("", tables[0].Description);
			Assert.AreEqual("store", tables[15].FullName);
			Assert.IsFalse(tables[15].IsSystem);
//			Assert.AreEqual("", tables[15].Description);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			tables = db.Tables;
			Assert.AreEqual(16, tables.Length);
			Assert.AreEqual("actor", tables[0].FullName);
			Assert.IsFalse(tables[0].IsSystem);
//			Assert.AreEqual("InnoDB", tables[0].Description);
			Assert.AreEqual("store", tables[15].FullName);
			Assert.IsFalse(tables[15].IsSystem);
//			Assert.AreEqual("InnoDB", tables[15].Description);

			// Sakila views
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.ALL, null);
			WrView[] views = db.Views;
			Assert.AreEqual(7, views.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.SYSTEM, null);
			views = db.Views;
			Assert.AreEqual(0, views.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER, null);
			views = db.Views;
			Assert.AreEqual(7, views.Length);
			Assert.AreEqual("actor_info", views[0].FullName);
			Assert.IsFalse(views[0].IsSystem);
//			Assert.AreEqual("", views[0].Description);
			Assert.AreEqual("staff_list", views[6].FullName);
			Assert.IsFalse(views[6].IsSystem);
//			Assert.AreEqual("", views[0].Description);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			views = db.Views;
			Assert.AreEqual(7, views.Length);
			Assert.AreEqual("actor_info", views[0].FullName);
			Assert.IsFalse(views[0].IsSystem);
//			Assert.AreEqual("VIEW", views[0].Description);
			Assert.AreEqual("staff_list", views[6].FullName);
			Assert.IsFalse(views[6].IsSystem);
//			Assert.AreEqual("VIEW", views[0].Description);

			// Sakila stored procedures
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.ALL, null);
			WrStoredProcedure[] procs = db.StoredProcedures;
			Assert.AreEqual(6, procs.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.SYSTEM, null);
			procs = db.StoredProcedures;
			Assert.AreEqual(0, procs.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER, null);
			procs = db.StoredProcedures;
			Assert.AreEqual(6, procs.Length);
			Assert.AreEqual("film_in_stock", procs[0].FullName);
			Assert.IsFalse(procs[0].IsSystem);
//			Assert.AreEqual("", procs[0].Description);
			Assert.AreEqual("rewards_report", procs[5].FullName);
			Assert.IsFalse(procs[5].IsSystem);
//			Assert.AreEqual("", procs[5].Description);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			procs = db.StoredProcedures;
			Assert.AreEqual(6, procs.Length);
			Assert.AreEqual("film_in_stock", procs[0].FullName);
			Assert.IsFalse(procs[0].IsSystem);
//			Assert.AreEqual("", procs[0].Description);
			Assert.AreEqual("rewards_report", procs[5].FullName);
			Assert.IsFalse(procs[5].IsSystem);
//			Assert.AreEqual("Provides a customizable report on best customers", procs[5].Description);

			// Sakila.staff columns
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER, null);
			WrColumn[] cols = db.Tables[14].Columns;
			Assert.AreEqual(11, cols.Length);
			Assert.AreEqual("staff_id", cols[0].FullName);
			Assert.AreEqual(DbType.Byte, cols[0].DbType);
//			Assert.AreEqual("", cols[0].Description);
			Assert.AreEqual("password", cols[9].FullName);
			Assert.AreEqual(DbType.AnsiString, cols[9].DbType);
//			Assert.AreEqual("", cols[9].Description);

			// Sakila.staff columns
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.DESCRIPTIONS, null);
			cols = db.Tables[14].Columns;
			Assert.AreEqual(11, cols.Length);
			Assert.AreEqual("staff_id", cols[0].FullName);
			Assert.AreEqual(DbType.Byte, cols[0].DbType);
//			Assert.AreEqual("", cols[0].Description);
			Assert.AreEqual("password", cols[9].FullName);
			Assert.AreEqual(DbType.AnsiString, cols[9].DbType);
//			Assert.AreEqual("", cols[9].Description);

			// test rental table pk-fk relationships were extracted
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			cols = db.Tables[13].Columns;
//bugbug			Assert.AreEqual("staff.staff_id", cols[5].PrimaryKey);
		}

		[Test]
		public void TestOracleEnumeration()
		{

			// get the database
			WrOdbcServer server = new WrOdbcServer(providerFactory, oracleOdbcProvider, oracleServerName);
			IWrDatabase db = server.CreateDatabase(null);
			WrCredentials cred = new WrCredentials(oracleUsername, oraclePassword);

			// we get system tables back

			// HR tables
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER, null);
			WrTable[] tables = db.Tables;
			Assert.IsTrue(tables.Length >= 14);
			bool gotCountries = false, gotRegions = false;
			foreach (WrTable tabl in tables)
				if (tabl.FullName == "COUNTRIES")
					gotCountries = true;
				else
					if (tabl.FullName == "REGIONS")
						gotRegions = true;
			Assert.IsTrue(gotCountries);
			Assert.IsTrue(gotRegions);

			// HR views
			WrView[] views = db.Views;
			Assert.IsTrue(views.Length >= 17);
			bool gotDetails = false;
			foreach (WrView vw in views)
				if (vw.FullName == "EMP_DETAILS_VIEW")
					gotDetails = true;
			Assert.IsTrue(gotDetails);

			// HR stored procedures
			WrStoredProcedure[] procs = db.StoredProcedures;
//			Assert.AreEqual(0, procs.Length);

			// HR.EMPLOYEES columns
			Trap.trap();
			WrColumn[] cols = db.Tables[5].Columns;
			Assert.AreEqual(11, cols.Length);
			Assert.AreEqual("EMPLOYEE_ID", cols[0].FullName);
			Assert.AreEqual(DbType.Decimal, cols[0].DbType);
			Assert.AreEqual("DEPARTMENT_ID", cols[10].FullName);
			Assert.AreEqual(DbType.Decimal, cols[10].DbType);
		}

		[Test]
		public void TestPostgreSqlEnumeration()
		{

			// bugbug - need a sample DB
			// get the database
			WrOdbcServer server = new WrOdbcServer(providerFactory, postgreOdbcProvider, postgreServerName);
			IWrDatabase db = server.CreateDatabase(postgreDatabaseName);
			WrCredentials cred = new WrCredentials(postgreUsername, postgrePassword);

			// SAMPLE tables
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			WrTable[] tables = db.Tables;
			Assert.AreEqual(21, tables.Length);
			Assert.AreEqual("actor", tables[0].FullName);
			Assert.IsFalse(tables[0].IsSystem);
			Assert.AreEqual("store", tables[20].FullName);
			Assert.IsFalse(tables[20].IsSystem);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.SYSTEM, null);
			tables = db.Tables;
			Assert.AreEqual(120, tables.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.ALL, null);
			tables = db.Tables;
			Assert.AreEqual(141, tables.Length);

			// SAMPLE views
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			WrView[] views = db.Views;
			Assert.AreEqual(10, views.Length);
			Assert.AreEqual("actor_info", views[0].FullName);
			Assert.IsFalse(views[0].IsSystem);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.SYSTEM, null);
			views = db.Views;
			Assert.AreEqual(0, views.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.ALL, null);
			views = db.Views;
			Assert.AreEqual(10, views.Length);

			// actor columns
			Trap.trap();
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			WrColumn[] cols = db.Tables[1].Columns;
			Assert.AreEqual(4, cols.Length);
			Assert.AreEqual("actor_id", cols[0].FullName);
//bugbug			Assert.AreEqual(DbType.String, cols[0].DbType);
			Assert.AreEqual("last_update", cols[3].FullName);
//			Assert.AreEqual(DbType.Decimal, cols[13].DbType);

			// SAMPLE stored procedures
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER, null);
			WrStoredProcedure[] procs = db.StoredProcedures;
			Assert.AreEqual(2921, procs.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.SYSTEM, null);
			procs = db.StoredProcedures;
			Assert.AreEqual(13, procs.Length);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.ALL, null);
			procs = db.StoredProcedures;
			Assert.AreEqual(2934, procs.Length);
		}

		[Test]
		public void TestSqlServerEnumeration()
		{
			// enum databases, make sure has test one
			WrOdbcServer server = new WrOdbcServer(providerFactory, "Sql Server", sqlServerServerName);
			Assert.AreEqual(sqlServerServerName, server.Name);
			IWrDatabase[] databases = server.GetDatabases(new WrCredentials(sqlServerUsername, sqlServerPassword));
			Assert.IsTrue(databases.Length == 0);

			WrOdbcDatabase db = new WrOdbcDatabase(providerFactory, sqlServerOdbcProvider, sqlServerServerName, sqlServerDatabaseName);

			// Northwind tables
			db.LoadMetadata(new WrCredentials(sqlServerUsername, sqlServerPassword), WrVendor.ELEM_OWNER.ALL, null);
			WrTable[] tables = db.Tables;
			Assert.AreEqual(13, tables.Length);

			db.LoadMetadata(new WrCredentials(sqlServerUsername, sqlServerPassword), WrVendor.ELEM_OWNER.SYSTEM, null);
			tables = db.Tables;
			Assert.AreEqual(0, tables.Length);

			db.LoadMetadata(new WrCredentials(sqlServerUsername, sqlServerPassword), WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			tables = db.Tables;
			Assert.AreEqual(13, tables.Length);
			Assert.AreEqual("Categories", tables[0].FullName);
			Assert.IsFalse(tables[0].IsSystem);
			Assert.AreEqual("Territories", tables[12].FullName);
			Assert.IsFalse(tables[12].IsSystem);

			// Northwind views
			db.LoadMetadata(new WrCredentials(sqlServerUsername, sqlServerPassword), WrVendor.ELEM_OWNER.ALL, null);
			WrView[] views = db.Views;
			Assert.IsTrue(views.Length > 300);

			db.LoadMetadata(new WrCredentials(sqlServerUsername, sqlServerPassword), WrVendor.ELEM_OWNER.SYSTEM, null);
			views = db.Views;
			Assert.IsTrue(views.Length > 200);

			db.LoadMetadata(new WrCredentials(sqlServerUsername, sqlServerPassword), WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			views = db.Views;
			Assert.AreEqual(16, views.Length);
			Assert.AreEqual("Alphabetical list of products", views[0].FullName);
			Assert.IsFalse(views[0].IsSystem);
			Assert.AreEqual("Summary of Sales by Year", views[15].FullName);
			Assert.IsFalse(views[15].IsSystem);

			// Northwind stored procedures
			db.LoadMetadata(new WrCredentials(sqlServerUsername, sqlServerPassword), WrVendor.ELEM_OWNER.USER, null);
			WrStoredProcedure[] procs = db.StoredProcedures;
			Assert.AreEqual(7, procs.Length);
			Assert.AreEqual("CustOrderHist", procs[0].FullName);
			Assert.AreEqual(2, procs[0].Parameters.Length);
			Assert.AreEqual("@CustomerID", procs[0].Parameters[1].Name);
			Assert.AreEqual(ParameterDirection.Input, procs[0].Parameters[1].Direction);

			db.LoadMetadata(new WrCredentials(sqlServerUsername, sqlServerPassword), WrVendor.ELEM_OWNER.SYSTEM, null);
			procs = db.StoredProcedures;
			Assert.IsTrue(procs.Length > 1200);

			db.LoadMetadata(new WrCredentials(sqlServerUsername, sqlServerPassword), WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			procs = db.StoredProcedures;
			Assert.AreEqual(7, procs.Length);
			Assert.AreEqual("CustOrderHist", procs[0].FullName);
			Assert.IsFalse(procs[0].IsSystem);
			Assert.AreEqual("Ten Most Expensive Products", procs[6].FullName);
			Assert.IsFalse(procs[6].IsSystem);

			// Northwind.Employees columns
			db.LoadMetadata(new WrCredentials(sqlServerUsername, sqlServerPassword), WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			WrColumn[] cols = db.Tables[4].Columns;
			Assert.AreEqual(18, cols.Length);
			Assert.AreEqual("EmployeeID", cols[0].FullName);
			Assert.AreEqual(DbType.Int32, cols[0].DbType);
			Assert.AreEqual("PhotoPath", cols[17].FullName);
			Assert.AreEqual(DbType.String, cols[17].DbType);


			// KailuaTest - all types
			db = new WrOdbcDatabase(providerFactory, sqlServerOdbcProvider, sqlServerServerName, "KailuaTest");
			db.LoadMetadata(new WrCredentials(sqlServerUsername, sqlServerPassword), WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			cols = db.Tables[0].Columns;
			Assert.AreEqual(24, cols.Length);
			// bugbug - assert each name & DbType. DbType is the key test
			// bugbug - Numeric is not in AllTypes - no translation.
		}

		// note - cannot insert a timestamp
		private readonly string[] colNames = {"kBinary", "kBoolean", "kChar", "kDateTime", "kDecimal", "kFloat", "kImage", "kInt", 
				"kMoney","kNchar", "kNtext", "kNvarchar", "kReal", "kSmallDateTime", "kSmallInt", "kSmallMoney", "kText",
				"kTinyInt", "kUniqueIdentifier", "kVarBinary", "kVarChar"};
		private readonly object[] colValues = {new byte[10], true, "dave", DateTime.Now, new Decimal(1.23), 4.56, new byte [5], 789,
				12.34, "shirley", "winter", "lucky", 56.78, DateTime.Now, 90, 1.23, "zoe",
				5, new Guid(), new byte [22], "cody"};

		[Test]
		public void TestSqlServerInsert()
		{

			using (WrConnection conn = new WrConnection("OdbcWrite"))
			{
				conn.Open();

				// empty it to start the same
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "delete from AllTypes";
					cmd.ExecuteNonQuery();
				}

				// test get the PK back
				long[] pk = new long[2];
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "insert into AllTypes (kNvarchar) values (?)";
					cmd.Set(0, "dave");
					pk[0] = cmd.ExecuteInsert("pk");
					Assert.IsTrue(pk[0] > 0);
				}

				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "insert into AllTypes (kNvarchar) values (?)";
					cmd.Set(0, "shirley");
					pk[1] = cmd.ExecuteInsert("pk");
					Assert.IsTrue(pk[1] > pk[0]);
				}

				// test insert each var type
				for (int ind = 0; ind < colNames.Length; ind++)
				{
					using (WrCommand cmd = conn.CreateWrCommand())
					{
						cmd.CommandText = "insert into AllTypes (" + colNames[ind] + ") values (?)";
						cmd.Set(0, colValues[ind]);
						cmd.ExecuteInsert("pk");
					}
				}

				// write all together
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					StringBuilder command = new StringBuilder("insert into AllTypes (");
					for (int ind = 0; ind < colNames.Length; ind++)
						command.Append(colNames[ind] + ",");
					command.Remove(command.Length - 1, 1);
					command.Append(") values (");
					for (int ind = 0; ind < colNames.Length; ind++)
						command.Append("?,");
					command.Remove(command.Length - 1, 1);
					command.Append(")");
					cmd.CommandText = command.ToString();

					for (int ind = 0; ind < colNames.Length; ind++)
						cmd.Set(ind, colValues[ind]);

					pk[0] = cmd.ExecuteInsert("pk");
				}

				// test read each var type
				byte[] data = new byte[20];
				char[] chrs = new char[20];
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "select * from AllTypes where pk = ?";
					cmd.Set(0, pk[0]);
					using (WrDataReader reader = cmd.WrExecuteReader())
					{
						Assert.IsTrue(reader.Read());
						Assert.AreEqual(pk[0], reader.GetInt64(0));
						Assert.AreEqual(4, reader.GetBytes(1, 0, data, 0, 4));
						Assert.AreEqual(colValues[1], reader.GetBoolean(2));
						Assert.AreEqual("dave      ", reader.GetString(3));
						Assert.AreEqual(((DateTime)colValues[3]).Date, reader.GetDateTime(4).Date);
						//						Assert.AreEqual(colValues[4], reader.GetDecimal(5));
						Assert.AreEqual(colValues[5], reader.GetValue(6));
						Assert.AreEqual(colValues[5], reader.GetDouble(6));
						Assert.AreEqual(4, reader.GetBytes(7, 0, data, 0, 4));
						Assert.AreEqual(colValues[7], reader.GetInt32(8));
						Assert.AreEqual(new decimal(12.3400), reader.GetDecimal(9));
						Assert.AreEqual(new decimal(12.3400), reader.GetDecimal(9));
						Assert.AreEqual("shirley   ", reader.GetString(10));
						Assert.AreEqual(colValues[10], reader.GetString(11));
						Assert.AreEqual(colValues[11], reader.GetString(12));
						Assert.AreEqual(colValues[12], reader.GetFloat(13));
						Assert.AreEqual(((DateTime)colValues[13]).Date, reader.GetDateTime(14).Date);
						Assert.AreEqual(colValues[14], reader.GetInt16(15));
						Assert.AreEqual(new decimal(1.2300), reader.GetDecimal(16));
						Assert.AreEqual(colValues[16], reader.GetString(17));
						Assert.IsTrue(reader.GetTimestamp(18) > 0);
						Assert.AreEqual(colValues[17], reader.GetByte(19));
						Assert.AreEqual(colValues[18], reader.GetGuid(20));
						Assert.AreEqual(4, reader.GetBytes(21, 0, data, 0, 4));
						Assert.AreEqual(colValues[20], reader.GetString(22));

						// remaining calls
						Assert.IsFalse(reader.IsDBNull(1));
						Assert.AreEqual("kBoolean", reader.GetName(2));
						Assert.AreEqual("char", reader.GetDataTypeName(3));
						Assert.AreEqual(typeof(DateTime), reader.GetFieldType(4));
						Assert.AreEqual(5, reader.GetOrdinal("kDecimal"));
						Assert.AreEqual(24, reader.FieldCount);
						Assert.AreEqual("lucky", reader[12]);
						Assert.AreEqual("lucky", reader["kNvarchar"]);

						object[] all = new object[24];
						Assert.AreEqual(24, reader.GetValues(all));
						Assert.AreEqual(pk[0], all[0]);
						Assert.AreEqual("lucky", all[12]);
					}
				}

				// test select top N
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "  select   * from AllTypes order by pk DESC  top  1  ";
					Assert.AreEqual("select top  1 * from AllTypes order by pk DESC", cmd.CommandText);
					using (WrDataReader reader = cmd.WrExecuteReader())
					{
						Assert.IsTrue(reader.Read());
						Assert.AreEqual(pk[0], reader.GetInt64(0));
					}
				}
			}
		}
	}
}
